import pymysql
from config.settings import DB_CONFIG

class DBOperations:
    def __init__(self):
        self.connection = None
        self.connect()

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=DB_CONFIG['host'],
                port=DB_CONFIG['port'],
                user=DB_CONFIG['user'],
                password=DB_CONFIG['password'],
                db=DB_CONFIG['db'],
                charset=DB_CONFIG['charset']
            )
        except Exception as e:
            print(f"数据库连接失败: {e}")
            raise

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

    def execute_query(self, sql, params=None):
        """执行查询语句"""
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params)
                return cursor.fetchall()
        except Exception as e:
            print(f"查询执行失败: {e}")
            raise

    def execute_update(self, sql, params=None):
        """执行更新语句"""
        try:
            with self.connection.cursor() as cursor:
                result = cursor.execute(sql, params)
                self.connection.commit()
                return result
        except Exception as e:
            self.connection.rollback()
            print(f"更新执行失败: {e}")
            raise

    def table_exists(self, table_name):
        """检查表是否存在"""
        sql = "SELECT * FROM information_schema.tables WHERE table_name = %s"
        result = self.execute_query(sql, table_name)
        return len(result) > 0

    def create_class_table(self, class_number):
        """创建班级表"""
        sql = f"""
        CREATE TABLE `mytest`.`{class_number}` (
            `id_num` INT NOT NULL,
            `userName` CHAR(255) NULL,
            `userEnglishName` CHAR(255) NULL,
            `gender` CHAR(255) NULL,
            `password` CHAR(255) NULL,
            `checked` CHAR(255) NULL,
            PRIMARY KEY (`id_num`)
        )
        """
        self.execute_update(sql)

    def create_sign_table(self, class_number):
        """创建签到表"""
        table_name = f"{class_number}_signdata"
        sql = f"""
        CREATE TABLE `mytest`.`{table_name}` (
            `userId` VARCHAR(20) NOT NULL,
            `userName` VARCHAR(50) NULL,
            `lessonName` VARCHAR(100) NULL,
            `classLocation` VARCHAR(100) NULL,
            `signDate` DATE NULL,
            `signTime` TIME NULL,
            `signState` CHAR(1) NULL,
            PRIMARY KEY (`userId`, `signDate`, `lessonName`)
        )
        """
        self.execute_update(sql)

    def add_user(self, class_number, user_data):
        """添加用户"""
        sql = f"""
        INSERT INTO `{class_number}` 
        (id_num, userName, userEnglishName, password, gender, checked) 
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        return self.execute_update(sql, user_data)

    def get_user(self, class_number, user_id):
        """获取用户信息"""
        sql = f"SELECT * FROM `{class_number}` WHERE id_num = %s"
        result = self.execute_query(sql, user_id)
        return result[0] if result else None

    def update_user_password(self, class_number, user_id, new_password):
        """更新用户密码"""
        sql = f"UPDATE `{class_number}` SET password = %s WHERE id_num = %s"
        return self.execute_update(sql, (new_password, user_id))

    def delete_user(self, class_number, user_id):
        """删除用户"""
        sql = f"DELETE FROM `{class_number}` WHERE id_num = %s"
        return self.execute_update(sql, user_id)

    def add_class(self, class_data):
        """添加班级"""
        sql = """
        INSERT INTO classtable 
        (majorNumber, majorName, classNumber, idTip, signStartTime, signEndTime) 
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        return self.execute_update(sql, class_data)

    def get_class(self, class_number):
        """获取班级信息"""
        sql = "SELECT * FROM classtable WHERE majorNumber = %s"
        result = self.execute_query(sql, class_number)
        return result[0] if result else None

    def update_class(self, class_data):
        """更新班级信息"""
        sql = """
        UPDATE classtable 
        SET majorName=%s, classNumber=%s, idTip=%s, signStartTime=%s, signEndTime=%s 
        WHERE majorNumber=%s
        """
        return self.execute_update(sql, class_data)

    def delete_class(self, class_number):
        """删除班级"""
        sql = "DELETE FROM classtable WHERE majorNumber = %s"
        return self.execute_update(sql, class_number)

    def add_lesson(self, lesson_data):
        """添加课程"""
        sql = """
        INSERT INTO lessontable 
        (toClass, lessonName, startDate, endDate, weekDay, startTime, endTime) 
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        return self.execute_update(sql, lesson_data)

    def get_lessons_by_class(self, class_number):
        """获取班级课程"""
        sql = "SELECT * FROM lessontable WHERE toClass = %s"
        return self.execute_query(sql, class_number)

    def update_lesson(self, lesson_data):
        """更新课程信息"""
        sql = """
        UPDATE lessontable 
        SET startDate=%s, endDate=%s, weekDay=%s, startTime=%s, endTime=%s 
        WHERE toClass=%s AND lessonName=%s
        """
        return self.execute_update(sql, lesson_data)

    def delete_lesson(self, class_number, lesson_name):
        """删除课程"""
        sql = "DELETE FROM lessontable WHERE toClass = %s AND lessonName = %s"
        return self.execute_update(sql, (class_number, lesson_name))

    def add_sign_record(self, class_number, sign_data):
        """添加签到记录"""
        table_name = f"{class_number}_signdata"
        sql = f"""
        INSERT INTO `{table_name}` 
        (userId, userName, lessonName, classLocation, signDate, signTime, signState) 
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        return self.execute_update(sql, sign_data)

    def get_sign_records(self, class_number, date=None, state=None, user_id=None):
        """获取签到记录"""
        table_name = f"{class_number}_signdata"
        sql = f"SELECT * FROM `{table_name}` WHERE 1=1"
        params = []
        
        if date:
            sql += " AND signDate = %s"
            params.append(date)
        
        if state:
            sql += " AND signState = %s"
            params.append(state)
            
        if user_id:
            sql += " AND userId = %s"
            params.append(user_id)
            
        return self.execute_query(sql, params)

    def get_all_classes(self):
        """获取所有班级"""
        sql = "SELECT * FROM classtable"
        return self.execute_query(sql)

    def get_all_lessons(self):
        """获取所有课程"""
        sql = "SELECT * FROM lessontable"
        return self.execute_query(sql)

    def get_user_sign_summary(self, class_number, user_id):
        """获取用户签到统计"""
        table_name = f"{class_number}_signdata"
        
        # 获取出勤次数
        success_sql = f"""
        SELECT COUNT(DISTINCT CONCAT(lessonName, signDate)) 
        FROM `{table_name}` 
        WHERE userId = %s AND signState = '1'
        """
        success_count = self.execute_query(success_sql, user_id)[0][0]
        
        # 获取迟到次数
        late_sql = f"""
        SELECT COUNT(DISTINCT CONCAT(lessonName, signDate)) 
        FROM `{table_name}` 
        WHERE userId = %s AND signState = '2'
        """
        late_count = self.execute_query(late_sql, user_id)[0][0]
        
        # 获取请假次数
        free_sql = f"""
        SELECT COUNT(DISTINCT CONCAT(lessonName, signDate)) 
        FROM `{table_name}` 
        WHERE userId = %s AND signState = '3'
        """
        free_count = self.execute_query(free_sql, user_id)[0][0]
        
        return {
            'success': success_count,
            'late': late_count,
            'free': free_count
        }

    def check_user_credentials(self, class_number, user_id, password):
        """验证用户凭据"""
        sql = f"SELECT password FROM `{class_number}` WHERE id_num = %s"
        result = self.execute_query(sql, user_id)
        
        if result and result[0][0] == password:
            return True
        return False

    def user_exists(self, class_number, user_id):
        """检查用户是否存在"""
        sql = f"SELECT * FROM `{class_number}` WHERE id_num = %s"
        result = self.execute_query(sql, user_id)
        return len(result) > 0

    def mark_user_checked(self, class_number, user_id):
        """标记用户已录入面部数据"""
        sql = f"UPDATE `{class_number}` SET checked = '是' WHERE id_num = %s"
        return self.execute_update(sql, user_id)

    def is_user_checked(self, class_number, user_id):
        """检查用户是否已录入面部数据"""
        sql = f"SELECT checked FROM `{class_number}` WHERE id_num = %s"
        result = self.execute_query(sql, user_id)
        return result[0][0] == '是' if result else False


class UserManager:
    """用户管理类"""
    
    def __init__(self):
        self.db = DBOperations()

    def register_user(self, user_id, password, name_ch, name_en, gender):
        """用户注册"""
        class_number = user_id[:8]
        
        # 检查班级是否存在
        class_info = self.db.get_class(class_number)
        if not class_info:
            return False, "班级不存在，请联系管理员"
        
        # 检查用户是否已存在
        if self.db.user_exists(class_number, user_id):
            return False, "用户已存在"
        
        # 添加用户
        user_data = (user_id, name_ch, name_en, password, gender, "否")
        try:
            self.db.add_user(class_number, user_data)
            return True, "注册成功"
        except Exception as e:
            return False, f"注册失败: {str(e)}"

    def login_user(self, user_id, password):
        """用户登录"""
        class_number = user_id[:8]
        
        # 检查班级表是否存在
        if not self.db.table_exists(class_number):
            return False, "班级不存在"
        
        # 验证用户凭据
        if self.db.check_user_credentials(class_number, user_id, password):
            return True, "登录成功"
        else:
            return False, "用户名或密码错误"

    def get_user_info(self, user_id):
        """获取用户信息"""
        class_number = user_id[:8]
        return self.db.get_user(class_number, user_id)


class ClassManager:
    """班级管理类"""
    
    def __init__(self):
        self.db = DBOperations()

    def create_class(self, class_number, class_name, class_size, id_format, start_time, end_time):
        """创建班级"""
        class_data = (class_number, class_name, class_size, id_format, start_time, end_time)
        
        try:
            self.db.add_class(class_data)
            # 创建对应的班级表
            self.db.create_class_table(class_number)
            return True, "班级创建成功"
        except Exception as e:
            return False, f"班级创建失败: {str(e)}"

    def get_class_info(self, class_number):
        """获取班级信息"""
        return self.db.get_class(class_number)

    def update_class_info(self, class_number, class_name, class_size, id_format, start_time, end_time):
        """更新班级信息"""
        class_data = (class_name, class_size, id_format, start_time, end_time, class_number)
        
        try:
            self.db.update_class(class_data)
            return True, "班级信息更新成功"
        except Exception as e:
            return False, f"班级信息更新失败: {str(e)}"

    def delete_class(self, class_number):
        """删除班级"""
        try:
            self.db.delete_class(class_number)
            return True, "班级删除成功"
        except Exception as e:
            return False, f"班级删除失败: {str(e)}"


class LessonManager:
    """课程管理类"""
    
    def __init__(self):
        self.db = DBOperations()

    def add_lesson(self, class_number, lesson_name, start_date, end_date, week_day, start_time, end_time):
        """添加课程"""
        lesson_data = (class_number, lesson_name, start_date, end_date, week_day, start_time, end_time)
        
        try:
            self.db.add_lesson(lesson_data)
            return True, "课程添加成功"
        except Exception as e:
            return False, f"课程添加失败: {str(e)}"

    def get_class_lessons(self, class_number):
        """获取班级课程"""
        return self.db.get_lessons_by_class(class_number)

    def update_lesson(self, class_number, lesson_name, start_date, end_date, week_day, start_time, end_time):
        """更新课程"""
        lesson_data = (start_date, end_date, week_day, start_time, end_time, class_number, lesson_name)
        
        try:
            self.db.update_lesson(lesson_data)
            return True, "课程更新成功"
        except Exception as e:
            return False, f"课程更新失败: {str(e)}"

    def delete_lesson(self, class_number, lesson_name):
        """删除课程"""
        try:
            self.db.delete_lesson(class_number, lesson_name)
            return True, "课程删除成功"
        except Exception as e:
            return False, f"课程删除失败: {str(e)}"


class SignManager:
    """签到管理类"""
    
    def __init__(self):
        self.db = DBOperations()

    def record_sign(self, class_number, user_id, user_name, lesson_name, location, date, time, state):
        """记录签到"""
        sign_data = (user_id, user_name, lesson_name, location, date, time, state)
        
        try:
            self.db.add_sign_record(class_number, sign_data)
            return True, "签到记录成功"
        except Exception as e:
            return False, f"签到记录失败: {str(e)}"

    def get_daily_sign_summary(self, class_number, date):
        """获取每日签到统计"""
        success_records = self.db.get_sign_records(class_number, date, '1')
        late_records = self.db.get_sign_records(class_number, date, '2')
        free_records = self.db.get_sign_records(class_number, date, '3')
        
        return {
            'success': len(success_records),
            'late': len(late_records),
            'free': len(free_records),
            'success_list': success_records,
            'late_list': late_records,
            'free_list': free_records
        }

    def get_user_sign_history(self, class_number, user_id):
        """获取用户签到历史"""
        return self.db.get_sign_records(class_number, user_id=user_id)

    def get_user_sign_statistics(self, class_number, user_id):
        """获取用户签到统计"""
        return self.db.get_user_sign_summary(class_number, user_id)


# 全局数据库操作实例
db_ops = DBOperations()
user_manager = UserManager()
class_manager = ClassManager()
lesson_manager = LessonManager()
sign_manager = SignManager()